package youleche.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.idao.IVehicleImageInfo;
import youleche.com.model.MVehicleImageInfo;

public class DVehicleImageInfo implements IVehicleImageInfo{
	/**
	 * 添加车辆图片信息
	 * @param con 数据库连接
	 * @param model 图片信息
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int addVehicleImageInfo(Connection con, MVehicleImageInfo model)
			throws Exception {
		PreparedStatement pre = null;
		try{
			String sql = "INSERT vehicleImageInfo(imageId,imagePath,imageType,sellerId,addTime,vehicleId,A1,A2,A3,A4,A5) VALUES(?,?,?,?,?,?,?,?,?,?,?);";
			pre = con.prepareStatement(sql);
			pre.setObject(1, model.getImageId());
			pre.setObject(2, model.getImagePath());
			pre.setObject(3, model.getImageType());
			pre.setObject(4, model.getSellerId());
			pre.setObject(5, model.getAddTime());
			pre.setObject(6, model.getVehicleId());
			pre.setObject(7, model.getA1());
			pre.setObject(8, model.getA2());
			pre.setObject(9, model.getA3());
			pre.setObject(10, model.getA4());
			pre.setObject(11, model.getA5());
	
			return pre.executeUpdate();

		}finally{
			JdbcHelper.free(null, pre);
		}
	}

	/**
	 * 获取首页图片信息
	 * @param con 数据库连接
	 * @return 返回首页图片信息（只有10条）
	 * @throws Exception 异常
	 */
	@Override
	public List<MVehicleImageInfo> getHomeImageInfo(Connection con)
			throws Exception {
		PreparedStatement pre = null;
		ResultSet rs = null;
		try{
			String sql = "select imageId,imagePath,imageType,sellerId,addTime,vehicleId from vehicleimageinfo where imageType=99  order by addTime desc limit 10;";
			
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			List<MVehicleImageInfo> list = null;
			MVehicleImageInfo img = null;
			if(rs != null){
				list = new LinkedList<MVehicleImageInfo>();
				while(rs.next()){
					img = new MVehicleImageInfo();
					img.setImageId(rs.getString(1));
					img.setImagePath(rs.getString(2));
					img.setImageType(rs.getString(3));
					img.setSellerId(rs.getString(4));
					img.setAddTime(rs.getDate(5));
					img.setVehicleId(rs.getString(6));
					list.add(img);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, pre);
		}
	}

	/**
	 * 通过车辆id获取车辆图片信息
	 * @param con 数据库连接
	 * @param vehicleId 车辆id
	 * @return 返回图片信息
	 * @throws Exception 异常
	 */
	@Override
	public List<MVehicleImageInfo> getImageInfoByVehicleId(Connection con,
			String vehicleId) throws Exception {
		PreparedStatement pre = null;
		ResultSet rs = null;
		try{
			String sql = "select imageId,imagePath,imageType,sellerId,addTime,vehicleId from vehicleimageinfo where vehicleId='"+vehicleId+"'";
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			List<MVehicleImageInfo> list = null;
			MVehicleImageInfo img = null;
			if(rs != null){
				list = new LinkedList<MVehicleImageInfo>();
				while(rs.next()){
					img = new MVehicleImageInfo();
					img.setImageId(rs.getString(1));
					img.setImagePath(rs.getString(2));
					img.setImageType(rs.getString(3));
					img.setSellerId(rs.getString(4));
					img.setAddTime(rs.getDate(5));
					img.setVehicleId(rs.getString(6));
					list.add(img);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, pre);
		}
		
	}

	/**
	 * 根据图片类型获取图片信息（DISTINCT后）
	 * @param con 数据库连接
	 * @param imageType 图片类型
	 * @param limit 大小
	 * @return 返回图片信息集合
	 * @throws Exception 异常
	 */
	@Override
	public List<MVehicleImageInfo> getImageByType_DISTINCT(Connection con,
			String imageType, int limit) throws Exception {
		PreparedStatement pre = null;
		ResultSet rs = null;
		try{
			String sql = "select imagePath,vehicleId,sellerId,count(distinct vehicleId) from vehicleimageinfo where imageType=" + imageType + " group by vehicleId order by addTime desc limit " + limit + ";";
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			List<MVehicleImageInfo> list = null;
			MVehicleImageInfo img = null;
			if(rs != null){
				list = new LinkedList<MVehicleImageInfo>();
				while(rs.next()){
					img = new MVehicleImageInfo();					
					img.setImagePath(rs.getString(1));
					img.setVehicleId(rs.getString(2));
					img.setSellerId(rs.getString(3));
					list.add(img);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, pre);
		}
	}

	/**
	 * 根据车辆信息Id删除图片ID
	 * @param con 数据库连接
	 * @param vehicleId 车辆ID
	 * @return 返回图片信息
	 * @throws Exception 异常
	 */
	@Override
	public int deleteImageByVehicleId(Connection con, String vehicleId)
			throws Exception {
		PreparedStatement ps = null;		
		try{
			String sql = "delete from vehicleimageinfo where vehicleId='" + vehicleId + "'";
			ps = con.prepareStatement(sql);
			
			return  ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

}
